﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Diagnostics;

namespace InhollandAirPortal {
  public class LoyalityDB : IDisposable {
    SqlConnection connection;
    public LoyalityDB() {
      ///<summary>
      ///Haalt connectionstring op uit de Web.Config. 
      ///De ConnectionString staat encrypted opgeslagen
      ///</summary>
      string connectionstring = ConfigurationManager.ConnectionStrings["DBConnectieString"].ToString();
      connection = new SqlConnection(connectionstring);
    }

    public void Dispose() {
      //Dispose functie van IDisposable, word automatisch uitgevoerd bij het eindigen van een using block
      connection.Dispose();
      connection.Close();
    }
    #region functies om waarden uit database op te halen
    public static int GetInt32Value(IDataRecord AReader, string AColumnName, int ADefaultValue) {
      if (!AReader.IsDBNull(AReader.GetOrdinal(AColumnName))) {
        return AReader.GetInt32(AReader.GetOrdinal(AColumnName));
      } else { return ADefaultValue; }
    }
    public static string GetStringValue(IDataRecord AReader, string AColumnName, string ADefaultValue) {
      if (!AReader.IsDBNull(AReader.GetOrdinal(AColumnName))) {
        return AReader.GetString(AReader.GetOrdinal(AColumnName));
      } else { return ADefaultValue; }
    }
    public static DateTime GetDateTimeValue(IDataRecord AReader, string AColumnName, DateTime ADefaultValue) {
      if (!AReader.IsDBNull(AReader.GetOrdinal(AColumnName))) {
        return AReader.GetDateTime(AReader.GetOrdinal(AColumnName));
      } else { return ADefaultValue; }
    }
    public static bool GetBoolValue(IDataRecord AReader, string AColumnName, bool ADefaultValue) {
      if (!AReader.IsDBNull(AReader.GetOrdinal(AColumnName))) {
        return AReader.GetBoolean(AReader.GetOrdinal(AColumnName));
      } else { return ADefaultValue; }
    }
    #endregion

    /*Try Catch functie om te kijken of de database beschikbaar is  */
    public bool CheckAvailability() {
      try {
        connection.Open();
        return true;
      } catch {
        return false;
      }
    }

    #region Stored procedures
    ///<summary>
    ///Hieronder volgen de stored procedures, geordend op entiteit waartoe ze behoren.
    ///In masterpage word gechecked of de database beschikbaar is (dmv de CheckAvailability() functie)
    ///Bij het niet beschikbaar zijn wordt doorverwezen naar de errorpagina, er zullen dus geen sql exceptions gegenereerd worden.
    /// </summary>
    /* User Stored Procedures */
    public IDataReader User_GetUserByID(int AUserID) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_GetUserByID", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter aParam = myCommand.Parameters.Add("@UserID", SqlDbType.Int);
      aParam.Value = AUserID;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }
    public IDataReader User_GetUserByLogin(string ALoginName) { //GEBRUIKT VOOR LOGIN
        connection.Open();
        SqlCommand myCommand = new SqlCommand("User_GetUserByLogin", connection);
        myCommand.CommandType = CommandType.StoredProcedure;
        SqlParameter aParam = myCommand.Parameters.Add("@LoginNaam", SqlDbType.NVarChar);
        aParam.Value = ALoginName;
        IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        return ReturnData;
    }
    public int User_CreateUser(string voornaam, string tussenvoegsel, string achternaam, string straatnaam, int huisnummer, string huisnummer_toevoeging, string postcode, string woonplaats, string land, string telefoonnummer, string password, string loginnam) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_CreateUser", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      #region sqlparams
      myCommand.Parameters.Add("@Voornaam", SqlDbType.NVarChar).Value = voornaam;
      if (!string.IsNullOrEmpty(tussenvoegsel)) { myCommand.Parameters.Add("@Tussenvoegsel", SqlDbType.NVarChar).Value = tussenvoegsel; }
      myCommand.Parameters.Add("@Achternaam", SqlDbType.NVarChar).Value = achternaam;
      myCommand.Parameters.Add("@Straatnaam", SqlDbType.NVarChar).Value = straatnaam;
      myCommand.Parameters.Add("@Huisnummer", SqlDbType.Int).Value = huisnummer;
      if (!string.IsNullOrEmpty(huisnummer_toevoeging)) { myCommand.Parameters.Add("@Huisnummer_toevoeging", SqlDbType.NVarChar).Value = huisnummer_toevoeging; }
      myCommand.Parameters.Add("@Postcode", SqlDbType.NVarChar).Value = postcode;
      myCommand.Parameters.Add("@Woonplaats", SqlDbType.NVarChar).Value = woonplaats;
      myCommand.Parameters.Add("@Land", SqlDbType.NVarChar).Value = land;
      myCommand.Parameters.Add("@Telefoonnummer", SqlDbType.NVarChar).Value = telefoonnummer;
      string passwordhash = PasswordHash.HashPassword(password);
      myCommand.Parameters.Add("@PasswordHash", SqlDbType.NVarChar).Value = passwordhash;
      myCommand.Parameters.Add("@Loginnaam", SqlDbType.NVarChar).Value = loginnam;

      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      #endregion
      return (int)Result.Value; // returns new ID for user
    }
    public int User_UpdateUser(int ID, string voornaam, string tussenvoegsel, string achternaam, string straatnaam, int huisnummer, string huisnummer_toevoeging, string postcode, string woonplaats, string land, string telefoonnummer) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_UpdateUser", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      #region sqlparams
      myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = ID;
      myCommand.Parameters.Add("@Voornaam", SqlDbType.NVarChar).Value = voornaam;
      myCommand.Parameters.Add("@Tussenvoegsel", SqlDbType.NVarChar).Value = tussenvoegsel;
      myCommand.Parameters.Add("@Achternaam", SqlDbType.NVarChar).Value = achternaam;
      myCommand.Parameters.Add("@Straatnaam", SqlDbType.NVarChar).Value = straatnaam;
      myCommand.Parameters.Add("@Huisnummer", SqlDbType.Int).Value = huisnummer;
      myCommand.Parameters.Add("@Huisnummer_toevoeging", SqlDbType.NVarChar).Value = huisnummer_toevoeging;
      myCommand.Parameters.Add("@Postcode", SqlDbType.NVarChar).Value = postcode;
      myCommand.Parameters.Add("@Woonplaats", SqlDbType.NVarChar).Value = woonplaats;
      myCommand.Parameters.Add("@Land", SqlDbType.NVarChar).Value = land;
      myCommand.Parameters.Add("@Telefoonnummer", SqlDbType.NVarChar).Value = telefoonnummer;

      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      #endregion

      return (int)Result.Value; // returns if the update succeeded
    }
    public int User_UpdatePasswordHash(int ID, string pNewPassword) {
      string newPasswordHash = PasswordHash.HashPassword(pNewPassword);

      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_UpdatePasswordHash", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      #region sqlparams
      myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = ID;
      myCommand.Parameters.Add("@PasswordHash", SqlDbType.NVarChar).Value = newPasswordHash;
      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      #endregion

      return (int)Result.Value;
    }
    public int User_UpdateLastLogin(int ID) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_UpdateLastLogin", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      #region sqlparams
      myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = ID;
      myCommand.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = DateTime.Now;
      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      #endregion

      return (int)Result.Value;
    }
    public string User_BoekReis(int UserID, int ReisID) {
      //SP Checkt of user genoeg punten heeft en zo-ja boekt de reis
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_BoekReis", connection);
      myCommand.CommandType = CommandType.StoredProcedure;

      myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
      myCommand.Parameters.Add("@ReisID", SqlDbType.Int).Value = ReisID;

      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Bit);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      int returnvalue = (int)Result.Value;
      switch (returnvalue) {
        /*-- RETURN CODES:
          -- 5: Reis is succesvol geboekt
          -- 10: FOUT Er is iets fout gegaan in het toevoegen aan de Boek tabel
          -- 11: FOUT Er is iets fout gegaan bij het update van de User (miles)
          -- 12: Reisdatum is groter dan */
        default:
        case 0:
          return MessageText.Message_AlgemeneFout;
        //break;
        case 5:
          return MessageText.Message_BoekingGeslaagd;
        //break;
        case 10:
          return MessageText.Message_FoutBijBoeken;
        //break;
        case 11:
          return MessageText.Message_FoutBijBoeken;
        //break;
        case 12:
          return MessageText.Message_ReisDatumIsAlGeweest;
        //break;
      }
    }
    public string User_AnnuleerBoeking(int UserID, string Boekingscode) {
      //SP Checkt of user genoeg punten heeft en zo-ja boekt de reis
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_AnnuleerBoeking", connection);
      myCommand.CommandType = CommandType.StoredProcedure;

      myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
      myCommand.Parameters.Add("@BoekingsCode", SqlDbType.NVarChar).Value = Boekingscode;

      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Bit);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      int returnvalue = (int)Result.Value;
      switch (returnvalue) {
        /*	-- RETURN CODES:
	          -- 5: Reis is succesvol geanuuleerd
	          -- 10: FOUT Er is iets fout gegaan bij het verwijdere uit de BOEKT tabel
	          -- 11: FOUT Er is iets fout gegaan bij het update van de User (miles)
	          -- 12: Reisdatum is groter dan de huidige datum */
        default:
        case 0:
          return MessageText.Message_AlgemeneFout;
        //break;
        case 5:
          return MessageText.Message_AnnuleringGeslaagd;
        //break;
        case 10:
          return MessageText.Message_FoutBijAnnulering;
        //break;
        case 11:
          return MessageText.Message_FoutBijAnnulering;
        //break;
        case 12:
          return MessageText.Message_ReisDatumIsAlGeweest;
        //break;
      }
    }
    public IDataReader User_GetBoekingsLijst(int UserID) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_GetBoekingsLijst", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter aParam = myCommand.Parameters.Add("@UserID", SqlDbType.Int);
      aParam.Value = UserID;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }
    public bool User_CheckAvailableLoginName(string ALoginName) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("User_CheckAvailableLoginName", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      #region sqlparams
      myCommand.Parameters.Add("@LoginNaam", SqlDbType.NVarChar).Value = ALoginName;
      SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
      Result.Direction = ParameterDirection.ReturnValue;
      myCommand.ExecuteNonQuery();
      #endregion
      
      //returns true if available
      return (int)Result.Value == 1;
    }
    /* Reis Stored Procedures */
    public IDataReader Reis_GetReisByID(int AReisID) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("Reis_GetReisByID", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter aParam = myCommand.Parameters.Add("@ReisID", SqlDbType.Int);
      aParam.Value = AReisID;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }
    public IDataReader Reis_GetReisByCode(string AReisCode) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("Reis_GetReisByCode", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter aParam = myCommand.Parameters.Add("@ReisCode", SqlDbType.NVarChar);
      aParam.Value = AReisCode;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }
    public IDataReader Reis_GetReisLijstByDatum(DateTime ADatum) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("Reis_GetReisLijstByDatum", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter aParam = myCommand.Parameters.Add("@Datum", SqlDbType.DateTime2);
      aParam.Value = ADatum;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }
    public IDataReader Reis_GetReisDatums() {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("Reis_GetReisDatums", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }
    /* Boeking Stored Procedures */
    public IDataReader Boeking_GetBoeking(string ABoekingsCode) {
      connection.Open();
      SqlCommand myCommand = new SqlCommand("Boeking_GetBoeking", connection);
      myCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter aParam = myCommand.Parameters.Add("@BoekingsCode", SqlDbType.NVarChar);
      aParam.Value = ABoekingsCode;
      IDataReader ReturnData = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
      return ReturnData;
    }

    /*Logging / Audit trail Stored Procedures */
    public int Logging_LogApplicationError(string AReqQueryString, DateTime ALogDateTime, string ARequestedURL, string AServerName, string AUserAgent, string AUserIP, string AUserAuthenticated, string AUserName, string AMessage, string ASource, string ATargetSite, string AStackTrace) {
      try {
        connection.Open();
        SqlCommand myCommand = new SqlCommand("Logging_LogApplicationError", connection);
        myCommand.CommandType = CommandType.StoredProcedure;
        /* parameters */
        myCommand.Parameters.Add("@RequestedQueryString", SqlDbType.NVarChar).Value = AReqQueryString;
        myCommand.Parameters.Add("@LogDate", SqlDbType.DateTime).Value = ALogDateTime;
        myCommand.Parameters.Add("@RequestedURL", SqlDbType.NVarChar).Value = ARequestedURL;
        myCommand.Parameters.Add("@ServerName", SqlDbType.NVarChar).Value = AServerName;
        myCommand.Parameters.Add("@UserAgent", SqlDbType.NVarChar).Value = AUserAgent;
        myCommand.Parameters.Add("@UserIP", SqlDbType.NVarChar).Value = AUserIP;
        myCommand.Parameters.Add("@UserAuthenticated", SqlDbType.NVarChar).Value = AUserAuthenticated;
        myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = AUserName;
        myCommand.Parameters.Add("@Message", SqlDbType.NVarChar).Value = AMessage;
        myCommand.Parameters.Add("@Source", SqlDbType.NVarChar).Value = ASource;
        myCommand.Parameters.Add("@TargetSite", SqlDbType.NVarChar).Value = ATargetSite;
        myCommand.Parameters.Add("@StackTrace", SqlDbType.NVarChar).Value = AStackTrace;

        SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
        Result.Direction = ParameterDirection.ReturnValue;
        myCommand.ExecuteNonQuery();
        return (int)Result.Value;
      } catch (SqlException ex) {
        // SQL Exception, return -1 zodat de error handling weet dat er iets fout is gegaan in de database connectie en schrijft de error naar een alternatief zodat hij geen exceptions blijft throwen
        return -1;
      }

    }
    public int Logging_WriteRequestLog(string AReqQueryString, DateTime ALogDateTime, string ARequestedURL, string AServerName, string AUserAgent, string AUserIP, string AUserAuthenticated, string AUserName) {
        connection.Open();
        SqlCommand myCommand = new SqlCommand("Logging_WriteRequestLog", connection);
        myCommand.CommandType = CommandType.StoredProcedure;
        /* parameters */
        myCommand.Parameters.Add("@RequestedQueryString", SqlDbType.NVarChar).Value = AReqQueryString;
        myCommand.Parameters.Add("@LogDate", SqlDbType.DateTime).Value = ALogDateTime;
        myCommand.Parameters.Add("@RequestedURL", SqlDbType.NVarChar).Value = ARequestedURL;
        myCommand.Parameters.Add("@ServerName", SqlDbType.NVarChar).Value = AServerName;
        myCommand.Parameters.Add("@UserAgent", SqlDbType.NVarChar).Value = AUserAgent;
        myCommand.Parameters.Add("@UserIP", SqlDbType.NVarChar).Value = AUserIP;
        myCommand.Parameters.Add("@UserAuthenticated", SqlDbType.NVarChar).Value = AUserAuthenticated;
        myCommand.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = AUserName;

        SqlParameter Result = myCommand.Parameters.Add("@Result", SqlDbType.Int);
        Result.Direction = ParameterDirection.ReturnValue;
        myCommand.ExecuteNonQuery();
        return (int)Result.Value;
    }
    #endregion
  }
}